﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using CharacterCreation.Data.QueryBuilders;

namespace CharacterCreation.Data {
    public class DataContext {

        public IEnumerable<T> Get<T>() where T : new()
        {
            if (!TableExists<T>()) throw new Exception("Table does not exist.");
            string order_by = typeof(T).GetProperty("Name") != null ? "[name]" : "[level]";
            return ExecuteQuery<T>(SelectBuilder.SelectQuery<T>() + " order by " + order_by);
        }

        public IEnumerable<T> Get<T>(string where, string orderby) where T : new()
        {
            if (!TableExists<T>()) throw new Exception("Table does not exist.");
            string query = SelectBuilder.SelectQuery<T>();

            if (!string.IsNullOrWhiteSpace(where)) query += " where " + where;
            if (!string.IsNullOrWhiteSpace(orderby)) query += " order by " + orderby;

            return ExecuteQuery<T>(query);
        }

        public T Get<T>(Guid id) where T : new() {
            if (!TableExists<T>()) throw new Exception("Table does not exist.");
            return ExecuteQuery<T>(string.Format("{0} where Id = '{1}'", SelectBuilder.SelectQuery<T>(), id)).FirstOrDefault();
        }

        public void Insert<T>(T entity) {
            if (!TableExists<T>()) throw new Exception("Table does not exist.");

            Guid id = (Guid)typeof(T).GetProperty("Id").GetValue(entity, null);
            if (id == Guid.Empty) typeof(T).GetProperty("Id").SetValue(entity, Guid.NewGuid(), null);

            string query = InsertBuilder.InsertQuery<T>(entity);
            ExecuteNonQuery(query);
        }


        public void Insert<T>(IEnumerable<T> entities)
        {
            if (!TableExists<T>()) throw new Exception("Table does not exist.");
            StringBuilder builder = new StringBuilder();
            foreach (var entity in entities)
            {
                Guid id = (Guid)typeof(T).GetProperty("Id").GetValue(entity, null);
                if (id == Guid.Empty) typeof(T).GetProperty("Id").SetValue(entity, Guid.NewGuid(), null);
                builder.Append(InsertBuilder.InsertQuery<T>(entity));
            }
            ExecuteNonQuery(builder.ToString());
        }

        public void Delete<T>(Guid id) {
            string query = DeleteBuilder.DeleteQuery<T>(id);
            ExecuteNonQuery(query);
        }

        public void Update<T>(T entity, string[] properties) {
            string query = UpdateBuilder.UpdateQuery<T>(entity, properties);
            ExecuteNonQuery(query);
        }

        public static bool TableExists<T>(bool create = true) {
            string query = TableBuilder.TableExistsQuery<T>();
            bool result = DataContext.ExecuteScallar<bool>(query);

            if (!result && create) {
                CreateTable<T>();
                return true;
            }

            return result;
        }

        public static void CreateTable<T>() {
            string createQuery = TableBuilder.CreateTableQuery<T>();
            DataContext.ExecuteNonQuery(createQuery);
        }

        public static void DropTable<T>() {
            var query = TableBuilder.DropTableQuery<T>();
            ExecuteNonQuery(query);
        }

        internal static IEnumerable<T> ExecuteQuery<T>(string query) where T : new() {
            SqlCommand command = new SqlCommand(query, ConnectionBuilder.CreateConnection());
            command.CommandType = System.Data.CommandType.Text;

            try {
                command.Connection.Open();
                IDataReader reader = command.ExecuteReader();

                List<T> result = new List<T>();
                while (reader.Read()) {
                    T t = new T();
                    foreach (var pInfo in typeof(T).GetProperties()) {
                        if (reader[pInfo.Name] != null) {
                            if (pInfo.PropertyType == typeof(double))
                            {
                                double x = double.Parse(reader[pInfo.Name].ToString());
                                pInfo.SetValue(t, x, null);
                            }
                            else {
                                pInfo.SetValue(t, reader[pInfo.Name], null);
                            }
                        }
                    }
                    result.Add(t);
                }

                return result;
            }
            catch (Exception ex) {
                throw new Exception(string.Format("Failed to execute query. {0}", ex.Message));
            }
            finally {
                if (command.Connection.State == System.Data.ConnectionState.Open) {
                    command.Connection.Close();
                    command.Dispose();
                }
            }
        }

        internal static int ExecuteNonQuery(string query) {
            SqlCommand command = new SqlCommand(query, ConnectionBuilder.CreateConnection());
            command.CommandType = System.Data.CommandType.Text;

            try {
                command.Connection.Open();
                return command.ExecuteNonQuery();
            }
            catch (Exception ex) {
                // ToDo error handling
                return -1;
            }
            finally {
                if (command.Connection.State == System.Data.ConnectionState.Open) {
                    command.Connection.Close();
                    command.Dispose();
                }
            }
        }

        internal static T ExecuteScallar<T>(string query) {
            SqlCommand command = new SqlCommand(query, ConnectionBuilder.CreateConnection());
            command.CommandType = System.Data.CommandType.Text;

            try {
                command.Connection.Open();
                var result = command.ExecuteScalar();
                return (T)result;
            }
            catch (Exception ex) {
                throw new Exception("fail...");
            }
            finally {
                if (command.Connection.State == System.Data.ConnectionState.Open) {
                    command.Connection.Close();
                    command.Dispose();
                }
            }
        }
    }
}
